﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class MaintenanceVisit
    {
        public MaintenanceVisit()
        {
        }

        public int SaveFromWeb(MaintenanceVisitInfo TBL_MAINTENANCE_VISIT)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MAINTENANCE_VISIT_SPAWEB_TMP 
                                SET 
                                    [AO_CODE] = @AO_CODE
                                    ,[NO_ACCOUNT] = @NO_ACCOUNT
                                    ,[DEBTOR_NAME] = @DEBTOR_NAME
                                    ,[OBJECTIVE_OF_VISIT] = @OBJECTIVE_OF_VISIT
                                    ,[ENTRY_BY] = @ENTRY_BY
                                    ,[ENTRY_DATE] = @ENTRY_DATE
                                    ,[UPDATE_BY] = @UPDATE_BY
                                    ,[UPDATE_DATE] = @UPDATE_DATE
                                    ,[IS_DELETED] = @IS_DELETED

                                   ,[RECORD_ID] = @RECORD_ID
                                   ,[EXPIRY_DATE] = @EXPIRY_DATE
                                   ,[EXTEND_DATE] = @EXTEND_DATE
                                   ,[BATCH_ID] = @BATCH_ID
                                   ,[CIF] = @CIF
                                   ,[DATA_TYPE] = @DATA_TYPE
                                   ,[IS_MAIN_DELETED] = '1'
                                   ,[NIPTeller] = @NIPTeller
                                   ,[ACT_TYPE] = @ACT_TYPE
                                WHERE (RECORD_ID = @RECORD_ID) AND (UNIT_CODE = @UNIT_CODE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MAINTENANCE_VISIT_SPAWEB_TMP
                                (
                                   [AO_CODE]
                                   ,[UNIT_CODE]
                                   ,[NO_ACCOUNT]
                                   ,[DEBTOR_NAME]
                                   ,[OBJECTIVE_OF_VISIT]
                                   ,[ENTRY_BY]
                                   ,[ENTRY_DATE]
                                   ,[UPDATE_BY]
                                   ,[UPDATE_DATE]
                                   ,[IS_DELETED]

                                   ,[RECORD_ID]
                                   ,[EXPIRY_DATE]
                                   ,[EXTEND_DATE]
                                   ,[BATCH_ID]
                                   ,[CIF]
                                   ,[DATA_TYPE]
                                   ,[IS_MAIN_DELETED]
                                   ,[NIPTeller]
                                   ,[ACT_TYPE]
                                ) 
                                VALUES
                                (
                                   @AO_CODE
                                   ,@UNIT_CODE
                                   ,@NO_ACCOUNT
                                   ,@DEBTOR_NAME
                                   ,@OBJECTIVE_OF_VISIT
                                   ,@ENTRY_BY
                                   ,@ENTRY_DATE
                                   ,@UPDATE_BY
                                   ,@UPDATE_DATE
                                   ,@IS_DELETED

                                   ,@RECORD_ID
                                   ,@EXPIRY_DATE
                                   ,@EXTEND_DATE
                                   ,@BATCH_ID
                                   ,@CIF
                                   ,@DATA_TYPE
                                   ,'1'
                                   ,@NIPTeller
                                   ,@ACT_TYPE
                                );
                    ");
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, TBL_MAINTENANCE_VISIT.AO_CODE);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_MAINTENANCE_VISIT.UNIT_CODE);
                db.AddInParameter(cmd, "@NO_ACCOUNT", DbType.String, TBL_MAINTENANCE_VISIT.NO_ACCOUNT);
                db.AddInParameter(cmd, "@DEBTOR_NAME", DbType.String, TBL_MAINTENANCE_VISIT.DEBTOR_NAME);
                db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, TBL_MAINTENANCE_VISIT.OBJECTIVE_OF_VISIT);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_MAINTENANCE_VISIT.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_MAINTENANCE_VISIT.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.UPDATE_DATE);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String, TBL_MAINTENANCE_VISIT.IS_DELETED);

                db.AddInParameter(cmd, "@RECORD_ID", DbType.String, TBL_MAINTENANCE_VISIT.RECORD_ID);
                db.AddInParameter(cmd, "@EXPIRY_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.EXPIRY_DATE);
                db.AddInParameter(cmd, "@EXTEND_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.EXTEND_DATE);
                db.AddInParameter(cmd, "@BATCH_ID", DbType.String, TBL_MAINTENANCE_VISIT.BATCH_ID);
                db.AddInParameter(cmd, "@CIF", DbType.String, TBL_MAINTENANCE_VISIT.CIF);
                db.AddInParameter(cmd, "@DATA_TYPE", DbType.String, TBL_MAINTENANCE_VISIT.DATA_TYPE);
                db.AddInParameter(cmd, "@NIPTeller", DbType.String, TBL_MAINTENANCE_VISIT.NIPTeller);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, TBL_MAINTENANCE_VISIT.ACT_TYPE);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                string temp = ex.ToString();
            }
            return result;
        }

        public int Save(MaintenanceVisitInfo TBL_MAINTENANCE_VISIT)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MAINTENANCE_VISIT_TMP 
                                SET 
                                    [DATE_VISIT] = @DATE_VISIT
                                    ,[NO_ACCOUNT] = @NO_ACCOUNT
                                    ,[DEBTOR_NAME] = @DEBTOR_NAME
                                    ,[OBJECTIVE_OF_VISIT] = @OBJECTIVE_OF_VISIT
                                    ,[ENTRY_BY] = @ENTRY_BY
                                    ,[ENTRY_DATE] = @ENTRY_DATE
                                    ,[UPDATE_BY] = @UPDATE_BY
                                    ,[UPDATE_DATE] = @UPDATE_DATE
                                    ,[IS_DELETED] = @IS_DELETED
                                WHERE (VISIT_ID = @VISIT_ID) AND (PROSPECT_ID = @PROSPECT_ID) AND (AO_CODE = @AO_CODE) AND (UNIT_CODE = @UNIT_CODE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MAINTENANCE_VISIT_TMP
                                (
                                       [VISIT_ID]
                                      ,[PROSPECT_ID]
                                      ,[AO_CODE]
                                      ,[UNIT_CODE]
                                      ,[DATE_VISIT]
                                      ,[NO_ACCOUNT]
                                      ,[DEBTOR_NAME]
                                      ,[OBJECTIVE_OF_VISIT]
                                      ,[ENTRY_BY]
                                      ,[ENTRY_DATE]
                                      ,[UPDATE_BY]
                                      ,[UPDATE_DATE]
                                      ,[IS_DELETED]
                                ) 
                                VALUES
                                (
                                       @VISIT_ID
                                      ,@PROSPECT_ID
                                      ,@AO_CODE
                                      ,@UNIT_CODE
                                      ,@DATE_VISIT
                                      ,@NO_ACCOUNT
                                      ,@DEBTOR_NAME
                                      ,@OBJECTIVE_OF_VISIT
                                      ,@ENTRY_BY
                                      ,@ENTRY_DATE
                                      ,@UPDATE_BY
                                      ,@UPDATE_DATE
                                      ,@IS_DELETED
                                );
                            ");
                db.AddInParameter(cmd, "@VISIT_ID", DbType.String, TBL_MAINTENANCE_VISIT.VISIT_ID);
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_MAINTENANCE_VISIT.PROSPECT_ID);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, TBL_MAINTENANCE_VISIT.AO_CODE);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_MAINTENANCE_VISIT.UNIT_CODE);
                db.AddInParameter(cmd, "@DATE_VISIT", DbType.DateTime, TBL_MAINTENANCE_VISIT.DATE_VISIT);
                db.AddInParameter(cmd, "@NO_ACCOUNT", DbType.String, TBL_MAINTENANCE_VISIT.NO_ACCOUNT);
                db.AddInParameter(cmd, "@DEBTOR_NAME", DbType.String, TBL_MAINTENANCE_VISIT.DEBTOR_NAME);
                db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, TBL_MAINTENANCE_VISIT.OBJECTIVE_OF_VISIT);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_MAINTENANCE_VISIT.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_MAINTENANCE_VISIT.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_MAINTENANCE_VISIT.UPDATE_DATE);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String, TBL_MAINTENANCE_VISIT.IS_DELETED);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public int GetRowNumber(string value)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                SELECT count(*) AS jmlData FROM(
	                SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB (NOLOCK) 
	                WHERE (DEBTOR_NAME LIKE '%' + @PARAM + '%') OR (CIF LIKE '%' + @PARAM + '%') 
                ) AS DATA
            ");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<MaintenanceVisitInfo> GetList(string value, int currPage, int dataPerPage)
        {
            List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            Database db = DBConnection.CreateDatabase();
            string sql = @"
                SELECT RECORD_ID, UNIT_CODE, AO_CODE, NIPTeller,
                    CIF, NO_ACCOUNT, DEBTOR_NAME, EXTEND_DATE, DATA_TYPE, EXPIRY_DATE,
                    (SELECT MS_STD_NAME 
                    FROM TBL_MS_STANDARD WHERE IS_ACTIVE = '1' AND 
                    MS_STD_ID = LogWithRowNumbers.OBJECTIVE_OF_VISIT AND
                    MS_STD_GRP_ID = 13 AND LTRIM(RTRIM(UPPER(MS_STD_TYPE))) = 'V') OBJECTIVE_OF_VISIT               
                    FROM ( 
	                SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * 
	                FROM TBL_MAINTENANCE_VISIT_SPAWEB (NOLOCK) WHERE (CIF LIKE @PARAM) OR (DEBTOR_NAME LIKE @PARAM) 
                )AS LogWithRowNumbers
                WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            //(CASE	WHEN AO_CODE <> '' THEN AO_CODE
            //        WHEN NIPTeller <> '' THEN NIPTeller
            //        ELSE ''
            //END) AS AOCODENIP, 

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitInfo()
                    {
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        AO_CODE = dr["AO_CODE"].ToString(),
                        NIPTeller = dr["NIPTeller"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                        DATA_TYPE = dr["DATA_TYPE"].ToString(),
                        EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                        OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString()
                    });
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitInfo> GetList(string value)
        {
            List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB WHERE (DEBTOR_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC");//(CIF_NUMBER LIKE '%' + @PARAM + '%') OR 
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitInfo
                        {
                            VISIT_ID = dr["VISIT_ID"].ToString(),
                            PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                            AO_CODE = dr["AO_CODE"].ToString(),
                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                            DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                            NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                            DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                            OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                            RECORD_ID = dr["RECORD_ID"].ToString(),
                            EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                            EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                            EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                            BATCH_ID = dr["BATCH_ID"].ToString(),
                            PROOF_CALL = dr["PROOF_CALL"].ToString(),
                            HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                            CIF = dr["CIF"].ToString(),
                            DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                            EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                            NIPTeller = dr["NIPTeller"].ToString(),
                        });
                }
                dr.Close();
            }
            return list;
        }

        //public List<MaintenanceVisitInfo> GetListByBranch(string BranchID)
        //{
        //    List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
        //    Database db = DBConnection.CreateDatabase();
        //    DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT WHERE UNIT_CODE LIKE '' + @BRANCH_ID + '' ORDER BY UPDATE_DATE DESC");
        //    db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
        //    using (IDataReader dr = db.ExecuteReader(cmd))
        //    {
        //        while (dr.Read())
        //        {
        //            list.Add(new MaintenanceVisitInfo(dr["VISIT_ID"].ToString(), dr["PROSPECT_ID"].ToString(), dr["AO_CODE"].ToString(),
        //                                                dr["UNIT_CODE"].ToString(), DateTime.Parse(dr["DATE_VISIT"].ToString()), dr["NO_ACCOUNT"].ToString(),
        //                                                dr["DEBTOR_NAME"].ToString(), dr["OBJECTIVE_OF_VISIT"].ToString(), dr["ENTRY_BY"].ToString(),
        //                                                DateTime.Parse(dr["ENTRY_DATE"].ToString()), dr["UPDATE_BY"].ToString(), DateTime.Parse(dr["UPDATE_DATE"].ToString()), dr["IS_DELETED"].ToString()));
        //        }
        //        dr.Close();
        //    }
        //    return list;
        //}

        public List<MaintenanceVisitInfo> GetListApproval(string value, string UpdateBy)
        {
            List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_TMP WHERE ((DEBITOR_NAME LIKE '%' + @PARAM + '%')) AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND LTRIM(RTRIM(BATCH_ID))='') ORDER BY UPDATE_DATE DESC");//(CIF_NUMBER LIKE '%' + @PARAM + '%') OR 
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitInfo(dr["VISIT_ID"].ToString(), dr["PROSPECT_ID"].ToString(), dr["AO_CODE"].ToString(),
                                                        dr["UNIT_CODE"].ToString(), DateTime.Parse(dr["DATE_VISIT"].ToString()), dr["NO_ACCOUNT"].ToString(),
                                                        dr["DEBTOR_NAME"].ToString(), dr["OBJECTIVE_OF_VISIT"].ToString(), dr["ENTRY_BY"].ToString(),
                                                        DateTime.Parse(dr["ENTRY_DATE"].ToString()), dr["UPDATE_BY"].ToString(), DateTime.Parse(dr["UPDATE_DATE"].ToString()), dr["IS_DELETED"].ToString()));
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitInfo> GetListApproval(string UpdateBy)
        {
            List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitInfo
                    {
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        ACT_TYPE = dr["ACT_TYPE"].ToString()
                    });
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitInfo> GetListApproval(string UpdateBy, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<MaintenanceVisitInfo> list = new List<MaintenanceVisitInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
                                @"SELECT  * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * 
                                    FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) 
                                    WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1'
                                )AS LogWithRowNumbers 
                                    WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitInfo
                    {
                        VISIT_ID = dr["VISIT_ID"].ToString(),
                        PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                        AO_CODE = dr["AO_CODE"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                        ENTRY_BY = dr["ENTRY_BY"].ToString(),
                        ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                        UPDATE_BY = dr["UPDATE_BY"].ToString(),
                        UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                        IS_DELETED = dr["IS_DELETED"].ToString(),
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                        EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                        EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                        BATCH_ID = dr["BATCH_ID"].ToString(),
                        PROOF_CALL = dr["PROOF_CALL"].ToString(),
                        HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                        EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                        NIPTeller = dr["NIPTeller"].ToString(),
                        ACT_TYPE = dr["ACT_TYPE"].ToString(),
                    });    
                }
                dr.Close();
            }
            return list;
        }

        public int GetListApprovalNumber(string UpdateBy)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                SELECT count(*) AS jmlData FROM ( 
                SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK)
                WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1') as Data
            ");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public MaintenanceVisitInfo Get(string RECORD_ID)
        {
            MaintenanceVisitInfo ci = new MaintenanceVisitInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB WHERE LTRIM(RTRIM(RECORD_ID)) = '' + @RECORD_ID + ''");
            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new MaintenanceVisitInfo
                    {
                        VISIT_ID = dr["VISIT_ID"].ToString(),
                        PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                        AO_CODE = dr["AO_CODE"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                        ENTRY_BY = dr["ENTRY_BY"].ToString(),
                        ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                        UPDATE_BY = dr["UPDATE_BY"].ToString(),
                        UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                        IS_DELETED = dr["IS_DELETED"].ToString(),
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                        EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                        EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                        BATCH_ID = dr["BATCH_ID"].ToString(),
                        PROOF_CALL = dr["PROOF_CALL"].ToString(),
                        HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                        EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                        NIPTeller = dr["NIPTeller"].ToString(),
                    };

                }
                dr.Close();
            }
            return ci;
        }

        public MaintenanceVisitInfo GetApprovalByID(string RECORD_ID)
        {
            MaintenanceVisitInfo ci = new MaintenanceVisitInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE LTRIM(RTRIM(RECORD_ID)) = '' + @RECORD_ID + '' AND IS_MAIN_DELETED = '1'");
            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new MaintenanceVisitInfo
                    {
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        ACT_TYPE = dr["ACT_TYPE"].ToString()
                    };

                }
                dr.Close();
            }
            return ci;
        }

        private DateTime ProcessTime(DateTime ProcesingDate)
        {
            return new DateTime(ProcesingDate.Year, ProcesingDate.Month, ProcesingDate.Day, DateTime.Now.Hour,
                DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond, ProcesingDate.Kind);
        }

        bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }

        private DateTime AddWorkDays(DateTime date, int workingDays, List<DateTime> Holidays)
        {
            int direction = workingDays < 0 ? -1 : 1;
            DateTime newDate = date.Date;
            while (workingDays != 0)
            {
                newDate = newDate.AddDays(direction);
                if (newDate.DayOfWeek != DayOfWeek.Saturday &&
                    newDate.DayOfWeek != DayOfWeek.Sunday &&
                    !Holidays.Contains(newDate))
                {
                    workingDays -= direction;
                }
            }
            return newDate;
        }

        public int Approve(string RECORD_ID, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("[MAINTENANCE_VISIT_APPROVAL]");

            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            return db.ExecuteNonQuery(cmd);
        }

        public int Reject(string VISIT_ID)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                            DELETE FROM TBL_MAINTENANCE_VISIT_TMP
                            WHERE LTRIM(RTRIM(VISIT_ID)) = '' + @VISIT_ID + '' AND IS_DELETED = '0';//LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @VISIT_ID + '' 
                            ");
            db.AddInParameter(cmd, "@VISIT_ID", DbType.String, VISIT_ID);
            return db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }
    }
}
